IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Bed]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Bed]
GO

/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetBed
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE TABLE [dbo].[Bed] (
   BedID   Varchar(30),
   [wardID]     Varchar(50),
   [BedNumber]     Varchar(50),
   [Description]     Varchar(50),
   [BedType]     Varchar(50),
   [BedStatus]     Varchar(50),
   [Remarks]     Varchar(50)
)
GO

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Bed TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetBed') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetBed
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_GetBed
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_GetBed
@vchBedID VARCHAR(30)
AS
   SELECT
       BedID,
       wardID,
       BedNumber,
       Description,
       BedType,
       BedStatus,
       Remarks
   FROM Bed
   WHERE BedID=@vchBedID
GO

GRANT EXEC ON dbo.SP_GetBed TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetBed') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetBed
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_SetBed
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_SetBed
@vchBedID Varchar(30),
@vchwardID    Varchar(50),
@vchBedNumber    Varchar(50),
@vchDescription    Varchar(50),
@vchBedType    Varchar(50),
@vchBedStatus    Varchar(50),
@vchRemarks    Varchar(50)
AS
DECLARE  @iRetValue     integer
   IF NOT EXISTS(SELECT     BedID
   FROM Bed
   WHERE   (BedID = @vchBedID)
   )

   BEGIN
   INSERT INTO Bed(
       BedID,
       wardID,
       BedNumber,
       Description,
       BedType,
       BedStatus,
       Remarks
  )
   VALUES (
      @vchBedID,
       @vchwardID,
       @vchBedNumber,
       @vchDescription,
       @vchBedType,
       @vchBedStatus,
       @vchRemarks
   )
   END
ELSE  IF EXISTS(SELECT     BedID
   FROM Bed
   WHERE   (BedID = @vchBedID)
   )

   BEGIN
   UPDATE Bed
   SET
       wardID = @vchwardID,
       BedNumber = @vchBedNumber,
       Description = @vchDescription,
       BedType = @vchBedType,
       BedStatus = @vchBedStatus,
       Remarks = @vchRemarks
  WHERE (BedID = @vchBedID)
   END

IF (@@ERROR <> 0)
   BEGIN
       declare @SPErrMsg varchar(800)
       SET @SPErrMsg = 'Error: Error in Stored procedure dbo.SP_SetBed for Loan# : ' + CAST (@vchBedID as varchar)
       RAISERROR (@SPErrMsg,16,1)
       Select @iRetValue = -1
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END
   
   RETURN @iRetValue
GO

GRANT EXEC ON dbo.SP_SetBed TO hms_usr
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_DeleteBed') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_DeleteBed
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/18/2008
'SP Name    : dbo.SP_DeleteBed
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE dbo.SP_DeleteBed
@vchBedID VARCHAR(30)
AS
   DELETE FROM Bed  WHERE   (BedID = @vchBedID)

GRANT EXEC ON dbo.SP_DeleteBed TO hms_usr
GO

